package nl.gamayun.whohasit;

import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class WhoHasItDBAdapter
{
	static final private String	TAG	= WhoHasItDBAdapter.class.getSimpleName();
	private static final String DATABASE_NAME = "whohasit.db";
	private static final String DATABASE_TABLE = "loanitems";
	public static final int DATABASE_VERSION = 4;
	private SQLiteDatabase db;
	private final Context context;
	private WhoHasItDBOpenHelper dbHelper;

	public static final String LOAN_ID = "_id";
	public static final int LOAN_ID_COLUMN = 0;
	public static final String LOAN_DATE = "date";
	public static final int LOAN_DATE_COLUMN = 1;
	public static final String HEADER = "header";
	public static final int HEADER_COLUMN = 2;
	public static final String DETAIL = "detail";
	public static final int DETAIL_COLUMN = 3;
	public static final String LOANER_NAME = "loaner_name";
	public static final int LOANER_NAME_COLUMN = 4;
	public static final String LOAN_TYPE = "type";
	public static final int LOAN_TYPE_COLUMN = 5;
	public static final String LOAN_RETURN_DATE = "return_date";
	public static final int LOAN_RETURN_DATE_COLUMN = 6;
	public static final String CONTACT_ID = "contact_id";
	public static final int CONTACT_ID_COLUMN = 7;
	
	private final String[] COLUMN_ARRAY = new String[] { LOAN_ID,
			LOAN_DATE, HEADER, DETAIL, LOANER_NAME, LOAN_TYPE, LOAN_RETURN_DATE, CONTACT_ID };

	public WhoHasItDBAdapter(Context _context)
	{
		Log.d(TAG, "WhoHasItDBAdapter");
		Log.d(TAG, "WhoHasItDBAdapter DB Version: " + DATABASE_VERSION);
		if (_context == null)
		{
			throw new NullPointerException("Context is null");
		}
		context = _context;
		dbHelper = new WhoHasItDBOpenHelper(context, DATABASE_NAME,	null, DATABASE_VERSION);

	}
	
	public void close()
	{
		Log.d(TAG, "close()");
		db.close();
	}

	public void open() throws SQLiteException
	{
		try
		{
			Log.d(TAG, "open().getWritableDatabase");
			db = dbHelper.getWritableDatabase();
		}
		catch (SQLiteException ex)
		{
			Log.d(TAG, "open().getReadableDatabase");
			db = dbHelper.getReadableDatabase();
		}
		Log.d(TAG, "DB Version: " + db.getVersion());
	}

	public int getDatabaseVersion()
	{
		return DATABASE_VERSION;
	}
	/**
	 * Create a new {@link ContentValues} object for a Loan
	 * @param _loan
	 * @return
	 */
	private ContentValues getContentValues (Loan _loan)
	{
		Log.d(TAG, "getContentValues("+_loan+")");
		ContentValues contentValues = new ContentValues();
		if (_loan.getLoanID() > 0)
			contentValues.put(LOAN_ID, _loan.getLoanID());
		else
			contentValues.putNull(LOAN_ID);
		contentValues.put(LOAN_DATE, _loan.getDate().getTimeInMillis());
		contentValues.put(HEADER, _loan.getHeader());
		contentValues.put(DETAIL, _loan.getDetail());
		contentValues.put(LOANER_NAME, _loan.getLoanerName());
		contentValues.put(LOAN_TYPE, _loan.getType().getTypeAsString());
		contentValues.put(LOAN_RETURN_DATE, _loan.getReturnDate().getTimeInMillis());
		contentValues.put(CONTACT_ID, _loan.getContactId());
		return contentValues;
	}
	
	// Insert a new Loan
	public long insertLoan(Loan _loan)
	{
		Log.d(TAG, "insertLoan("+_loan+")");
		return db.insert(DATABASE_TABLE, null, getContentValues(_loan));
	}
	
	// Remove a task based on its index
	public boolean removeLoan(long _loanId)
	{
		Log.d(TAG, "removeLoan("+_loanId+")");
		return db.delete(DATABASE_TABLE, LOAN_ID + "=" + _loanId, null) > 0;
	}

	// Update a task
	public boolean updateLoan(long _loanId, Loan _loan)
	{
		Log.d(TAG, "updateLoan("+_loan+")");
		return db.update(DATABASE_TABLE, getContentValues(_loan), LOAN_ID + "=" + _loanId, null) > 0;
	}

	public Cursor getAllLoanItemsCursor()
	{
		Log.d(TAG, "getAllLoanItemsCursor()");
		return db.query(DATABASE_TABLE, COLUMN_ARRAY , null, null, null, null, null);
	}

	public Cursor setCursorToLoanItem(long _rowIndex) throws SQLException
	{
		Log.d(TAG, "setCursorToLoanItem("+_rowIndex+")");
		Cursor result = db.query(true, DATABASE_TABLE, COLUMN_ARRAY, LOAN_ID + "=" + _rowIndex, null, null, null, null, null);
		if ((result.getCount() == 0) || !result.moveToFirst())
		{
			throw new SQLException("No loan items found for row: " + _rowIndex);
		}
		return result;
	}

	public Loan getLoan(long _rowIndex) throws SQLException
	{
		Log.d(TAG, "getLoan("+_rowIndex+")");
		Cursor cursor = db.query(true, DATABASE_TABLE, COLUMN_ARRAY, LOAN_ID + "=" + _rowIndex, null, null, null, null, null);
		if ((cursor.getCount() == 0) || !cursor.moveToFirst())
		{
			throw new SQLException("No loan item found for row: " + _rowIndex);
		}
		long loanID = cursor.getLong(LOAN_ID_COLUMN);
		Calendar date = Calendar.getInstance(); 
		date.setTimeInMillis(cursor.getLong(LOAN_DATE_COLUMN));
		String header = cursor.getString(HEADER_COLUMN);
		String detail = cursor.getString(DETAIL_COLUMN);
		long contactId = cursor.getLong(CONTACT_ID_COLUMN);
		String loanderName = cursor.getString(LOANER_NAME_COLUMN);
		TYPE type = TYPE.getType(cursor.getString(LOAN_TYPE_COLUMN));
		Calendar returnDate = Calendar.getInstance(); 
		returnDate.setTimeInMillis(cursor.getLong(LOAN_RETURN_DATE_COLUMN));
		Loan result = new Loan(loanID, date, returnDate, header, detail, contactId, loanderName, type);
		Log.d(TAG, "getLoan("+_rowIndex+") - result:" + result.toString());
		cursor.close();
		return result;
	}
	
	private static class WhoHasItDBOpenHelper extends SQLiteOpenHelper
	{
		static final private String	TAG2	= WhoHasItDBOpenHelper.class.getSimpleName();
		public WhoHasItDBOpenHelper(Context context, String name,
				CursorFactory factory, int version)
		{
			super(context, name, factory, version);
			Log.d(TAG2, "WhoHasItDBOpenHelper()");
		}

		// SQL Statement to create a new database.
		private static final String	DATABASE_CREATE	= "create table "
								+ DATABASE_TABLE
								+ " ("
								+ LOAN_ID
								+ " integer primary key autoincrement, "
								+ LOAN_DATE
								+ " long," 
								+ HEADER
								+ " text not null, "
								+ DETAIL
								+ " text, "
								+ LOANER_NAME
								+ " text, "
								+ LOAN_TYPE
								+ " text not null,"
								+ LOAN_RETURN_DATE
								+ " long," 
								+ CONTACT_ID
								+ " integer" 
								+ ");"
								;

		@Override
		public void onCreate(SQLiteDatabase _db)
		{
			Log.d(TAG2, "onCreate("+_db+")");
			_db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion)
		{
			Log.d(TAG2, "onUpgrade("+_db+","+_oldVersion+","+_newVersion+")");
			if (_oldVersion == 1)
			{
				Log.w(TAG2, "Upgrading from version " + _oldVersion + " to "
						+ _newVersion + ", which will update the type column");
				updateVersion1to2(_db);
				updateVersionTo3(_db);
				updateVersionTo4(_db);
			}
			if (_oldVersion == 2)
			{
				Log.w(TAG2, "Upgrading from version " + _oldVersion + " to "
						+ _newVersion + ", which will add new column "+ LOAN_RETURN_DATE);
				updateVersionTo3(_db);
				updateVersionTo4(_db);

			}
			if (_oldVersion == 3)
			{
				Log.w(TAG2, "Upgrading from version " + _oldVersion + " to "
						+ _newVersion + ", which will add new column "+ CONTACT_ID);
				updateVersionTo4(_db);
			}
		}
		
		private void updateVersion1to2(SQLiteDatabase _db)
		{
			
			if (!_db.isReadOnly())
			{
				StringBuffer sql = new StringBuffer("update " + DATABASE_TABLE + " set " + LOAN_TYPE + "=");
				sql.append("case ");
				sql.append(" when "+ LOAN_TYPE + "='To' then '0' ");
				sql.append(" when "+ LOAN_TYPE + "='From' then '1' ");
				sql.append(" when "+ LOAN_TYPE + "='Aan' then '0' ");
				sql.append(" when "+ LOAN_TYPE + "='Van' then '1' ");
				sql.append(" end; ");

				_db.execSQL(sql.toString());
			}
		}

		private void updateVersionTo3(SQLiteDatabase _db)
		{
			
			if (!_db.isReadOnly())
			{
				StringBuffer sql = new StringBuffer("alter table ");
				sql.append(DATABASE_TABLE);
				sql.append(" add column ");
				sql.append(LOAN_RETURN_DATE);
				sql.append("; ");
				
				_db.execSQL(sql.toString());
				
				Calendar newReturnDate = Calendar.getInstance();
				newReturnDate.add(Calendar.MONTH, 1);
				sql = new StringBuffer("update ");
				sql.append(DATABASE_TABLE);
				sql.append(" set ");
				sql.append(LOAN_RETURN_DATE + "=");
				sql.append(newReturnDate.getTimeInMillis());
				sql.append("; ");

				_db.execSQL(sql.toString());
			}
		}

		private void updateVersionTo4(SQLiteDatabase _db)
		{
			
			if (!_db.isReadOnly())
			{
				StringBuffer sql = new StringBuffer("alter table ");
				sql.append(DATABASE_TABLE);
				sql.append(" add column ");
				sql.append(CONTACT_ID);
				sql.append(" INTEGER; ");
				
				_db.execSQL(sql.toString());
				
			}
		}

	}

}
